﻿Imports DTO
Imports BUS
Imports System.IO


Public Class UserControlExport
    Dim lvwDanhSachPhieuGuiTien As New ListView()
    Dim lvwDanhSachPhieuRutTien As New ListView()
    Private Sub UserControlExport_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        btnExport.Enabled = False

        lvwDanhSachPhieuGuiTien.Location = New Point(7, 19)
        lvwDanhSachPhieuGuiTien.Size = New Size(534, 167)
        lvwDanhSachPhieuGuiTien.Visible = False
        lvwDanhSachPhieuGuiTien.Columns.Add("STT", 50, HorizontalAlignment.Left)
        lvwDanhSachPhieuGuiTien.Columns.Add("Mã Phiếu Gửi", 100, HorizontalAlignment.Left)
        lvwDanhSachPhieuGuiTien.Columns.Add("Mã Sổ", 100, HorizontalAlignment.Left)
        lvwDanhSachPhieuGuiTien.Columns.Add("Tên Khách Hàng", 100, HorizontalAlignment.Left)
        lvwDanhSachPhieuGuiTien.Columns.Add("Số Tiền Gửi", 100, HorizontalAlignment.Left)
        lvwDanhSachPhieuGuiTien.Columns.Add("Ngày Gửi", 100, HorizontalAlignment.Left)
        lvwDanhSachPhieuGuiTien.Font = New Font("Arial", 8.25)
        lvwDanhSachPhieuGuiTien.View = View.Details
        lvwDanhSachPhieuGuiTien.GridLines = DataGridLineStyle.Solid
        gbxDanhSach.Controls.Add(lvwDanhSachPhieuGuiTien)
        ''''''
        lvwDanhSachPhieuRutTien.Location = New Point(7, 19)
        lvwDanhSachPhieuRutTien.Size = New Size(534, 167)
        lvwDanhSachPhieuRutTien.Visible = False
        lvwDanhSachPhieuRutTien.Columns.Add("STT", 50, HorizontalAlignment.Left)
        lvwDanhSachPhieuRutTien.Columns.Add("Mã Phiếu Rút", 100, HorizontalAlignment.Left)
        lvwDanhSachPhieuRutTien.Columns.Add("Mã Sổ", 100, HorizontalAlignment.Left)
        lvwDanhSachPhieuRutTien.Columns.Add("Tên Khách Hàng", 100, HorizontalAlignment.Left)
        lvwDanhSachPhieuRutTien.Columns.Add("Số Tiền Rut", 100, HorizontalAlignment.Left)
        lvwDanhSachPhieuRutTien.Columns.Add("Ngày Rut", 100, HorizontalAlignment.Left)
        lvwDanhSachPhieuRutTien.Font = New Font("Arial", 8.25)
        lvwDanhSachPhieuRutTien.View = View.Details
        lvwDanhSachPhieuRutTien.GridLines = DataGridLineStyle.Solid
        gbxDanhSach.Controls.Add(lvwDanhSachPhieuRutTien)

    End Sub

    Private Sub btnDanhSachPhieuGoiTien_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        btnExport.Enabled = True
        lvwDanhSachPhieuGuiTien.Visible = True
        lvwDanhSach.Visible = False
        lvwDanhSachPhieuRutTien.Visible = False
        gbxDanhSach.Text = "Danh Sách Phiếu Gửi Tiền"

        Dim pgtBus As New PhieuGoiTienBUS()
        Dim dt As New DataTable()
        lvwDanhSachPhieuGuiTien.Items.Clear()
        dt = pgtBus.LayThongTinDanhSachPhieuGui()
        For i As Integer = 0 To dt.Rows.Count - 1
            Dim lv As ListViewItem = lvwDanhSachPhieuGuiTien.Items.Add((i + 1).ToString())
            lv.SubItems.Add(dt.Rows(i)("MaPhieuGoi"))
            lv.SubItems.Add(dt.Rows(i)("MaSo"))
            lv.SubItems.Add(dt.Rows(i)("MaKhachHang"))
            lv.SubItems.Add(dt.Rows(i)("SoTienGoi"))
            lv.SubItems.Add(dt.Rows(i)("NgayGoi"))
        Next

    End Sub

    Private Sub btnDanhSachPhieuRutTien_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        btnExport.Enabled = True
        lvwDanhSachPhieuGuiTien.Visible = False
        lvwDanhSach.Visible = False
        lvwDanhSachPhieuRutTien.Visible = True
        gbxDanhSach.Text = "Danh Sách Phiếu Rút Tiền"

        Dim prtBus As New PhieuRutTienBUS()
        Dim dt As New DataTable()
        dt = prtBus.LayThongTinDanhSachPhieuRutTien()
        lvwDanhSachPhieuRutTien.Items.Clear()
        For i As Integer = 0 To dt.Rows.Count - 1
            Dim lv As ListViewItem = lvwDanhSachPhieuRutTien.Items.Add((i + 1).ToString())
            lv.SubItems.Add(dt.Rows(i)("MaPhieuRut"))
            lv.SubItems.Add(dt.Rows(i)("MaSo"))
            lv.SubItems.Add(dt.Rows(i)("TenKhachHang"))
            lv.SubItems.Add(dt.Rows(i)("SoTienRut"))
            lv.SubItems.Add(dt.Rows(i)("NgayRut"))
        Next
    End Sub

    Private Sub btnDanhSachSoTietKiem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        btnExport.Enabled = True
        lvwDanhSachPhieuGuiTien.Visible = False
        lvwDanhSach.Visible = True
        lvwDanhSachPhieuRutTien.Visible = False
        gbxDanhSach.Text = "Danh Sách Sổ Tiết Kiệm"
        Dim stkBus As New SoTietKiemBUS()
        Dim dt As New DataTable()
        lvwDanhSach.Items.Clear()
        dt = stkBus.LayThongTinDanhSachSoTietKiem()
        For i As Integer = 0 To dt.Rows.Count - 1
            Dim lv As ListViewItem = lvwDanhSach.Items.Add((i + 1).ToString())
            lv.SubItems.Add(dt.Rows(i)("MaSo"))
            lv.SubItems.Add(dt.Rows(i)("TenLoaiTietKiem"))
            lv.SubItems.Add(dt.Rows(i)("MaLoaiTietKiem"))
            lv.SubItems.Add(dt.Rows(i)("TenKhachHang"))
            lv.SubItems.Add(dt.Rows(i)("MaKhachHang"))
            lv.SubItems.Add(dt.Rows(i)("SoTienGoi"))
            lv.SubItems.Add(dt.Rows(i)("NgayMoSo"))
            lv.SubItems.Add(dt.Rows(i)("SoDu"))
            lv.SubItems.Add(dt.Rows(i)("CMND"))
            lv.SubItems.Add(dt.Rows(i)("DiaChi"))
        Next

    End Sub

    Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
        'DataSet As DataTable, ByVal _List_Col As String, ByVal Path As String)
        '- _Dataset: luu d?u li?u c?n Export 
        '- _List_Col: Danh sách tên c?t hi?n th?. Tên c?t cách nhau d?u ','
        '- Path: Ðu?ng d?n luu file excel
        Dim stkBus As New SoTietKiemBUS()
        Dim pgtBus As New PhieuGoiTienBUS()
        Dim prtBus As New PhieuRutTienBUS()
        Dim _Dataset As New DataTable()
        Dim _List_Col As String
        If lvwDanhSach.Visible = True Then
            _Dataset = stkBus.LayThongTinDanhSachSoTietKiem()
            _List_Col = "Mã Sổ,Tên Loại Tiết Kiệm, Mã Loại Tiết Kiệm,Tên Khách Hàng,Mã Khách Hàng,Số Tiền Gởi,"
            _List_Col += "Ngày Mở Sổ,Số Dư,CMND,Địa Chỉ"
        Else
            If lvwDanhSachPhieuGuiTien.Visible = True Then
                _Dataset = pgtBus.LayThongTinDanhSachPhieuGui()
                _List_Col = "Mã Phiếu Gởi,Mã Sổ,Mã Khách Hàng,Số Tiền Gởi,Ngày Gởi"
            Else
                If lvwDanhSachPhieuRutTien.Visible = True Then
                    _Dataset = pgtBus.LayThongTinDanhSachPhieuGui()
                    _List_Col = "Mã Phiếu Rút,Mã Sổ,Mã Khách Hàng,Số Tiền Rút,Ngày Rút"
                End If
            End If
        End If
        If _List_Col.Length = 0 Then
            Return
        End If
        Dim col, row As Integer
        Dim arrListcol() As String
        arrListcol = _List_Col.Split(",")

        If _Dataset.Rows.Count <= 0 Then
            Exit Sub
        End If

        Dim Excel As Object = CreateObject("Excel.Application")
        If Excel Is Nothing Then
            MessageBox.Show("Ðể xuất ra Excel, máy bạn phải cài đặt bộ phần mềm Microsoft Office.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            Return
        End If

        Dim fd As New SaveFileDialog()
        fd.Filter = "Name .xls|*.xls"
        If fd.ShowDialog = DialogResult.OK Then
            Dim Path As String
            Path = fd.FileName
            'Export to Excel process
            Try
                With Excel
                    .SheetsInNewWorkbook = 1
                    .Workbooks.Add()
                    .Worksheets(1).Select()

                    Dim i As Integer = 2
                    .cells(2, i).value = "STT"
                    .cells(2, i).EntireRow.Font.Bold = True
                    For col = 0 To arrListcol.Length - 1
                        i += 1
                        .cells(2, i).value = arrListcol(col)
                        .cells(2, i).EntireRow.Font.Name = "Times New Roman"
                        .cells(2, i).EntireRow.Font.Size = 12
                        .cells(2, i).EntireRow.Font.Bold = True

                    Next
                    i = 3
                    Dim k As Integer = 3
                    For col = 0 To _Dataset.Columns.Count - 1
                        i = 3
                        For row = 0 To _Dataset.Rows.Count - 1
                            .Cells(i, 2).Value = i - 2
                            .Cells(i, k).Value = _Dataset.Rows(row).ItemArray(col)
                            i += 1
                        Next
                        k += 1
                    Next
                    .Columns.AutoFit()
                    .ActiveCell.Worksheet.SaveAs(Path)
                End With
                System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
                Excel = Nothing
                MessageBox.Show("Xuất dữ liệu thành công.", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End If

        Dim pro() As Process = System.Diagnostics.Process.GetProcessesByName("EXCEL")
        For Each i As Process In pro
            i.Kill()
        Next
    End Sub
End Class
